﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PromotionApp.Utility;
using System.Data;
using System.Data.SqlClient;
using PromotionApp.Model;

namespace PromotionApp.DAL
{
    public class CategoryDAL : BaseDAL
    {
        #region Private Variable
        
        private static CategoryDAL _CategoryDALDAL = null;

        #endregion

        #region Propery
        #endregion

        #region Constructor

        private CategoryDAL() : base()
        {
            
        }

        #endregion

        #region Public Method

        public DataSet GetSummaryStat(string strDomainName)
        {
            string strSql = "MsCategorySelectSummaryStat";
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("DomainName", strDomainName));
            DataSet ds = this._SQLHelper.GetDataSet(strSql, list, CommandType.StoredProcedure);

            return ds;
        }

        public void Update(Category category)
        {
            string strSql = "MsCategoryUpdate";
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("CategoryId", category.CategoryId));
            list.Add(new SqlParameter("CategoryName", category.CategoryName));
            list.Add(new SqlParameter("DomainName", category.DomainName));
            list.Add(new SqlParameter("LanguageCode", category.LanguageCode));
            list.Add(new SqlParameter("StatusCode", category.StatusCode));
            list.Add(new SqlParameter("UpdatedBy", category.UpdatedBy));
            this._SQLHelper.ExecuteNonQuery(strSql, list, CommandType.StoredProcedure);
        }

        public int CountByCategoryNameInsert(string strCategoryName)
        {
            string strSql = "MsCategoryCountByCategoryNameInsert";
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("CategoryName", strCategoryName));

            return Convert.ToInt32(this._SQLHelper.ExecuteScalar(strSql, list, CommandType.StoredProcedure));
        }

        public int CountByCategoryNameUpdate(int categoryId, string strCategoryName)
        {
            string strSql = "MsCategoryCountByCategoryNameUpdate";
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("CategoryId", categoryId));
            list.Add(new SqlParameter("CategoryName", strCategoryName));

            return Convert.ToInt32(this._SQLHelper.ExecuteScalar(strSql, list, CommandType.StoredProcedure));
        }

        public void Add(Category category)
        {
            string strSql = "MsCategoryInsert";
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("CategoryName", category.CategoryName));
            list.Add(new SqlParameter("DomainName", category.DomainName));
            list.Add(new SqlParameter("LanguageCode", category.LanguageCode));
            list.Add(new SqlParameter("StatusCode", category.StatusCode));
            list.Add(new SqlParameter("CreatedBy", category.CreatedBy));
            this._SQLHelper.ExecuteNonQuery(strSql, list, CommandType.StoredProcedure);
        }

        public DataSet GetCatgoryById(int categoryId)
        {
            string strSql = "MsCategorySelectById";
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("CategoryId", categoryId));
            DataSet ds = this._SQLHelper.GetDataSet(strSql, list, CommandType.StoredProcedure);

            return ds;
        }

        public void Delete(int categoryId)
        {
            string strSql = "MsCategoryDelete";
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("CategoryId", categoryId));
            this._SQLHelper.ExecuteNonQuery(strSql, list, CommandType.StoredProcedure);
        }

        public DataSet GetAllDomain()
        {
            string strSql = "MsCategorySelectAllDomain";
            List<SqlParameter> list = new List<SqlParameter>();            
            DataSet ds = this._SQLHelper.GetDataSet(strSql, list, CommandType.StoredProcedure);

            return ds;
        }

        public DataSet GetCategoryByDomainName(string strDomainName)
        {
            string strSql = "MsCategorySelectByDomainName";
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("DomainName", strDomainName));
            DataSet ds = this._SQLHelper.GetDataSet(strSql, list, CommandType.StoredProcedure);

            return ds;
        }

        public DataSet GetCategorySummaryFromDb(string strSql)
        {
            List<SqlParameter> list = new List<SqlParameter>();
            DataSet ds = this._SQLHelper.GetDataSet(strSql, list, CommandType.Text);

            return ds;
        }

        public static CategoryDAL GetInstance()
        {
            if (_CategoryDALDAL == null)
                _CategoryDALDAL = new CategoryDAL();

            return _CategoryDALDAL;
        }       

        #endregion
    }
}